﻿using _1142063_1142080_1142098.Models.DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace _1142063_1142080_1142098.Models.Services
{
    public class TourService
    {
        public static Tuple<List<TourSearch>,int> TraCuuTourTrongNuoc(string noiDi, string noiDen, DateTime? ngayDi, decimal? minGia, decimal? maxGia,int start,int end)
        {
            var tourSearchList = new List<TourSearch>();
            int count = 0;
            var myDataProvider = new MyDataProvider();
            myDataProvider.Open();
            var parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@NoiDi", SqlDbType.NVarChar));
            parameterList[parameterList.Count - 1].Value = noiDi;
            parameterList.Add(new SqlParameter("@NoiDen", SqlDbType.NVarChar));
            parameterList[parameterList.Count - 1].Value = noiDen;
            parameterList.Add(new SqlParameter("@NgayGioDi", SqlDbType.DateTime));
            if (ngayDi != null)
            {
                parameterList[parameterList.Count - 1].Value = ngayDi;
            }
            else
            {
                parameterList[parameterList.Count - 1].Value = DBNull.Value;
            }
            parameterList.Add(new SqlParameter("@MinGiaTourCoBanNguoiLonVietNam", SqlDbType.Decimal));
            if (minGia != null)
            {
                parameterList[parameterList.Count - 1].Value = minGia;
            }
            else
            {
                parameterList[parameterList.Count - 1].Value = DBNull.Value;
            }
            parameterList.Add(new SqlParameter("@MaxGiaTourCoBanNguoiLonVietNam", SqlDbType.Decimal));
            if (maxGia != null)
            {
                parameterList[parameterList.Count - 1].Value = maxGia;
            }
            else
            {
                parameterList[parameterList.Count - 1].Value = DBNull.Value;
            }
            parameterList.Add(new SqlParameter("@start", SqlDbType.Int));
            parameterList[parameterList.Count - 1].Value = start;
            parameterList.Add(new SqlParameter("@end", SqlDbType.Int));
            parameterList[parameterList.Count - 1].Value = end;
            tourSearchList = Utility.ToList<TourSearch>(myDataProvider.ExcuteQueryWithParameters("spu_tourDuLichTrongNuoc_traCuu", parameterList));
            parameterList.RemoveAt(parameterList.Count - 1);
            parameterList.RemoveAt(parameterList.Count - 1);
            count = (int)myDataProvider.ExcuteScalarWithParameters("spu_tourDuLichTrongNuoc_laySoLuong", parameterList);
            myDataProvider.Close();
            int soDong = (int)Math.Ceiling((double)count / 100);
            return new Tuple<List<TourSearch>, int>(tourSearchList,soDong);
        }

        public static Tuple<List<TourSearch>,int> TraCuuTourNuocNgoai(string noiDi, string noiDen, DateTime? ngayDi, decimal? minGia, decimal? maxGia,int start,int end)
        {
            var tourSearchList = new List<TourSearch>();
            int count = 0;
            var myDataProvider = new MyDataProvider();
            myDataProvider.Open();
            var parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@NoiDi", SqlDbType.NVarChar));
            parameterList[parameterList.Count - 1].Value = noiDi;
            parameterList.Add(new SqlParameter("@NoiDen", SqlDbType.NVarChar));
            parameterList[parameterList.Count - 1].Value = noiDen;
            parameterList.Add(new SqlParameter("@NgayDi", SqlDbType.DateTime));
            if (ngayDi != null)
            {
                parameterList[parameterList.Count - 1].Value = ngayDi;
            }
            else
            {
                parameterList[parameterList.Count - 1].Value = DBNull.Value;
            }
            parameterList.Add(new SqlParameter("@GiaTu", SqlDbType.Decimal));
            if (minGia != null)
            {
                parameterList[parameterList.Count - 1].Value = minGia;
            }
            else
            {
                parameterList[parameterList.Count - 1].Value = DBNull.Value;
            }
            parameterList.Add(new SqlParameter("@GiaDen", SqlDbType.Decimal));
            if (maxGia != null)
            {
                parameterList[parameterList.Count - 1].Value = maxGia;
            }
            else
            {
                parameterList[parameterList.Count - 1].Value = DBNull.Value;
            }
            parameterList.Add(new SqlParameter("@start", SqlDbType.Int));
            parameterList[parameterList.Count - 1].Value = start;
            parameterList.Add(new SqlParameter("@end", SqlDbType.Int));
            parameterList[parameterList.Count - 1].Value = end;
            tourSearchList = Utility.ToList<TourSearch>(myDataProvider.ExcuteQueryWithParameters("spu_tourDuLichNuocNgoai_traCuu", parameterList));
            parameterList.RemoveAt(parameterList.Count-1);
            parameterList.RemoveAt(parameterList.Count - 1);
            count = (int)myDataProvider.ExcuteScalarWithParameters("spu_tourDuLichNuocNgoai_laySoLuong", parameterList);
            myDataProvider.Close();
            int soDong = (int)Math.Ceiling((double)count / 100);
            return new Tuple<List<TourSearch>, int>(tourSearchList, soDong);
        }

        public static Tuple<List<ChuongTrinhTour>, TourSearch> LayChuongTrinhTourTrongNuoc(DateTime idTour)
        {
            var chuongTrinhTourList = new List<ChuongTrinhTour>();
            var tour = new TourSearch();
            var myDataProvider = new MyDataProvider();
            myDataProvider.Open();
            var parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@id", SqlDbType.DateTime));
            parameterList[parameterList.Count - 1].Value = idTour;
            chuongTrinhTourList = Utility.ToList<ChuongTrinhTour>(myDataProvider.ExcuteQueryWithParameters("spu_tourDuLichTrongNuoc_layChuongTrinhTour", parameterList));
            tour = Utility.ToList<TourSearch>(myDataProvider.ExcuteQueryWithParameters("spu_tourDuLichTrongNuoc_layTheoId", parameterList))[0];
            myDataProvider.Close();
            return new Tuple<List<ChuongTrinhTour>, TourSearch>(chuongTrinhTourList, tour);
        }

        public static Tuple<List<ChuongTrinhTour>, TourSearch> LayChuongTrinhTourNuocNgoai(DateTime idTour)
        {
            var chuongTrinhTourList = new List<ChuongTrinhTour>();
            var tour = new TourSearch();
            var myDataProvider = new MyDataProvider();
            myDataProvider.Open();
            var parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@IdTour", SqlDbType.DateTime));
            parameterList[parameterList.Count - 1].Value = idTour;
            chuongTrinhTourList = Utility.ToList<ChuongTrinhTour>(myDataProvider.ExcuteQueryWithParameters("spu_tourDuLichNuocNgoai_layChuongTrinhTour", parameterList));
            tour = Utility.ToList<TourSearch>(myDataProvider.ExcuteQueryWithParameters("spu_tourDuLichNuocNgoai_layTheoId", parameterList))[0];
            myDataProvider.Close();
            return new Tuple<List<ChuongTrinhTour>, TourSearch>(chuongTrinhTourList, tour);
        }

        public static ChiTietTourTrongNuoc LayChiTietTourTrongNuoc(DateTime idTour)
        {
            var chiTietTourTrongNuoc = new ChiTietTourTrongNuoc();
            var myDataProvider = new MyDataProvider();
            myDataProvider.Open();
            var parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@id", SqlDbType.DateTime));
            parameterList[parameterList.Count - 1].Value = idTour;
            chiTietTourTrongNuoc = Utility.ToList<ChiTietTourTrongNuoc>(myDataProvider.ExcuteQueryWithParameters("spu_tourDuLichTrongNuoc_layChiTietTour", parameterList))[0];
            myDataProvider.Close();
            return chiTietTourTrongNuoc;
        }

        public static ChiTietTourNuocNgoai LayChiTietTourNuocNgoai(DateTime idTour)
        {
            var chiTietTourNuocNgoai = new ChiTietTourNuocNgoai();
            var myDataProvider = new MyDataProvider();
            myDataProvider.Open();
            var parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@IdTour", SqlDbType.DateTime));
            parameterList[parameterList.Count - 1].Value = idTour;
            chiTietTourNuocNgoai = Utility.ToList<ChiTietTourNuocNgoai>(myDataProvider.ExcuteQueryWithParameters("spu_tourDuLichNuocNgoai_layChiTietTour", parameterList))[0];
            myDataProvider.Close();
            return chiTietTourNuocNgoai;
        }

        public static ChiTietTourTrongNuoc LayTourTrongNuoc(DateTime idTour)
        {
            var chiTietTourTrongNuoc = new ChiTietTourTrongNuoc();
            var myDataProvider = new MyDataProvider();
            myDataProvider.Open();
            var parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@id", SqlDbType.DateTime));
            parameterList[parameterList.Count - 1].Value = idTour;
            chiTietTourTrongNuoc = Utility.ToList<ChiTietTourTrongNuoc>(myDataProvider.ExcuteQueryWithParameters("spu_tourDuLichTrongNuoc_layTheoId", parameterList))[0];
            myDataProvider.Close();
            return chiTietTourTrongNuoc;
        }

        public static ChiTietTourNuocNgoai LayTourNuocNgoai(DateTime idTour)
        {
            var chiTietTourNuocNgoai = new ChiTietTourNuocNgoai();
            var myDataProvider = new MyDataProvider();
            myDataProvider.Open();
            var parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@IdTour", SqlDbType.DateTime));
            parameterList[parameterList.Count - 1].Value = idTour;
            chiTietTourNuocNgoai = Utility.ToList<ChiTietTourNuocNgoai>(myDataProvider.ExcuteQueryWithParameters("spu_tourDuLichNuocNgoai_layTheoId", parameterList))[0];
            myDataProvider.Close();
            return chiTietTourNuocNgoai;
        }

        public static PhieuDangKyTour LayPhieuDangKyByMaPhieu(Guid maPhieu,bool isTrongNuoc)
        {
            PhieuDangKyTour phieuDangKyTour = null;
            var myDataProvider = new MyDataProvider();
            myDataProvider.Open();
            var parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@MaPhieu", SqlDbType.UniqueIdentifier));
            parameterList[parameterList.Count - 1].Value = maPhieu;
            if (isTrongNuoc)
            {
                var tempList = Utility.ToList<PhieuDangKyTour>(myDataProvider.ExcuteQueryWithParameters("spu_phieuDangKyTourTrongNuoc_layByMaPhieu",parameterList));
                if (tempList.Count > 0)
                {
                    phieuDangKyTour = tempList[0];
                }
            }
            else
            {
                var tempList = Utility.ToList<PhieuDangKyTour>(myDataProvider.ExcuteQueryWithParameters("spu_phieuDangKyTourNuocNgoai_layByMaPhieu", parameterList));
                if (tempList.Count > 0)
                {
                    phieuDangKyTour = tempList[0];
                }
            }
            myDataProvider.Close();
            return phieuDangKyTour;
        }

        public static List<ChiTietPhieuDangKyTour> LayChiTietPhieuDangKyByIdPhieuDangKy(DateTime idTour,bool isTrongNuoc)
        {
            var chiTietPhieuDangKyTourList = new List<ChiTietPhieuDangKyTour>();
            var myDataProvider = new MyDataProvider();
            myDataProvider.Open();
            var parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@IdPhieuDangKy", SqlDbType.DateTime));
            parameterList[parameterList.Count - 1].Value = idTour;
            if (isTrongNuoc)
            {
                chiTietPhieuDangKyTourList = Utility.ToList<ChiTietPhieuDangKyTour>(myDataProvider.ExcuteQueryWithParameters("spu_chiTietPhieuDangKyTourTrongNuoc_layByIdPhieuDangKy", parameterList));
            }
            else
            {
                chiTietPhieuDangKyTourList = Utility.ToList<ChiTietPhieuDangKyTour>(myDataProvider.ExcuteQueryWithParameters("spu_chiTietPhieuDangKyTourNuocNgoai_layByIdPhieuDangKy", parameterList));
            }
            myDataProvider.Close();
            return chiTietPhieuDangKyTourList;
        }
    }
}